package br.com.q7solucoes.commons.wordpress;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

/**
 * Classe que permite persistencias em bancos Wordpress.
 *
 * @author Elenilson Vieira - elenilson[at]q7solucoes.com.br
 * @since 9 de setembro 2011
 * @version 1.0
 * @see www.q7solucoes.com.br
 */
public class WPPostDAO {

    /**
     * Nome do Drive JDBC
     */
    public final static String JDBC_DRIVER_NAME = "com.mysql.jdbc.Driver"; // MySQL MM JDBC driver
    public final static String THUMBNAIL_ID_VALUE = "_thumbnail_id"; 
    public final static String POST_TABLE_NAME = "wp_%s_posts"; 
    public final static String TAGS_TABLE_NAME = "wp_%s_terms"; 
    public final static String IMAGE_TABLE_NAME = POST_TABLE_NAME; 
    
    /*** Nomes das colunas ***/
    public final static String TITLE_COLUMN_NAME = "post_title"; 
    public final static String CONTENT_COLUMN_NAME = "post_content"; 
    public final static String LINK_COLUMN_NAME = "post_name"; 
    public final static String DATE_COLUMN_NAME = "post_date"; 
    public final static String ID_COLUMN_NAME = "post_id"; 

    public final static String CATEGORY_TAXONOMY_VALUE = "category"; 
    public final static String TAG_TAXONOMY_VALUE = "post_tag"; 
    
    private String mysqlDatabase;
    private String mysqlDatabaseHost;
    private String mysqlDatabaseUser;
    private String mysqlDatabasePassword;
    private String mysqlDatabaseCode;
    private Connection connection;

    public WPPostDAO(String mysqlDatabase, String mysqlDatabaseHost, String mysqlDatabaseUser, 
            String mysqlDatabasePassword, String mysqlDatabaseCode) throws ClassNotFoundException, SQLException {
        this.mysqlDatabase = mysqlDatabase;
        this.mysqlDatabaseHost = mysqlDatabaseHost;
        this.mysqlDatabaseUser = mysqlDatabaseUser;
        this.mysqlDatabasePassword = mysqlDatabasePassword;
        this.mysqlDatabaseCode = mysqlDatabaseCode;

        init();
    }

    public static String getJDBC_DRIVER_NAME() {
        return JDBC_DRIVER_NAME;
    }

    public String getMysqlDatabase() {
        return mysqlDatabase;
    }

    public String getMysqlDatabaseCode() {
        return mysqlDatabaseCode;
    }

    public String getMysqlDatabaseHost() {
        return mysqlDatabaseHost;
    }

    public String getMysqlDatabasePassword() {
        return mysqlDatabasePassword;
    }

    public String getMysqlDatabaseUser() {
        return mysqlDatabaseUser;
    }

    /**
     * Insere um post no banco do Wordpress
     *
     * @param wPPost o post a ser inserido
     * 
     * @return o numero de linhas afetadas
     */
    public int add(WPPost wPPost) throws SQLException{
        StringBuilder sql = new StringBuilder("INSERT INTO ");
        sql
                .append("wp_").append(mysqlDatabaseCode).append("_posts(") 
                .append("post_author, ")
                .append("post_date, ")
                .append("post_content, ")
                .append("post_title, ")
                .append("post_status, ")
                .append("comment_status, ")
                .append("ping_status, ")
                .append("post_name, ")
                .append("post_type, ")
                .append("post_excerpt, ")
                .append("to_ping, ")
                .append("pinged, ")
                .append("post_content_filtered")
                .append(") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        
        PreparedStatement pstmt = connection.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
        
        /*** Insere os valores ***/
        pstmt.setInt(1, 1);
        pstmt.setDate(2, new java.sql.Date(wPPost.getDate().getTime()));
        pstmt.setString(3, wPPost.getContent());
        pstmt.setString(4, wPPost.getTitle());
        pstmt.setString(5, "publish");
        pstmt.setString(6, "open");
        pstmt.setString(7, "open");
        pstmt.setString(8, wPPost.getLink());
        pstmt.setString(9, "post");
        pstmt.setString(10, "");
        pstmt.setString(11, "");
        pstmt.setString(12, "");
        pstmt.setString(13, "");
        
        int rowAffected = pstmt.executeUpdate();
        
        /*** Se as linhas afetadas forem a de um post (a ideia do metodo) ***/
        if(rowAffected == 1){
            /*** Posiciona o cursor e pega o id do post ***/
            ResultSet resultSet = pstmt.getGeneratedKeys();
            resultSet.next();
            int postId = resultSet.getInt(1);
            
            List<String> tagsAndCats = new ArrayList<String>();
            tagsAndCats.addAll(wPPost.getCategories());
            tagsAndCats.addAll(wPPost.getTags());
            
            /*** Adiciona as Tags e Categorias ***/
            for (String tagCat : tagsAndCats) {
                /*** Pega o id da categoria ***/
                sql = new StringBuilder("SELECT term_id FROM ");
                sql
                        .append("wp_").append(mysqlDatabaseCode).append("_terms")
                        .append(" WHERE name = '").append(tagCat).append("'");
                
                Statement stmt = connection.createStatement();
                resultSet = stmt.executeQuery(sql.toString());
                
                if(resultSet.next()){
                    int tagId = resultSet.getInt("term_id");

                    /*** Cria o relacionamento da categoria com o post ***/
                    sql = new StringBuilder("INSERT INTO ");
                    sql
                            .append("wp_").append(mysqlDatabaseCode).append("_term_relationships (")
                            .append("object_id, ")
                            .append("term_taxonomy_id")
                            .append(") VALUES (?, ?)");

                    pstmt = connection.prepareStatement(sql.toString());
                    pstmt.setInt(1, postId);
                    pstmt.setInt(2, tagId);

                    pstmt.executeUpdate();
                }
            }
            
            /*** Adiciona a imagem destacada ***/
            if(wPPost.getImage() != null && !wPPost.getImage().trim().isEmpty()){
                Statement stmt = connection.createStatement();
                
                int beginIndex = wPPost.getImage().indexOf("uploads/") + "uploads/".length();
                String preparedImage = wPPost.getImage().substring(beginIndex);
                resultSet = stmt.executeQuery(String.format("SELECT id FROM wp_%s_posts WHERE guid LIKE '%%%s%%'", mysqlDatabaseCode, preparedImage));
                
                if(resultSet.next()){
                    int imageId = resultSet.getInt("id");
                    
                    sql = new StringBuilder("INSERT INTO ");
                    sql
                            .append("wp_").append(mysqlDatabaseCode).append("_postmeta (")
                            .append("post_id, ")
                            .append("meta_key, ")
                            .append("meta_value")
                            .append(") VALUES (?, ?, ?)");
                    
                    pstmt = connection.prepareStatement(sql.toString());
                    pstmt.setInt(1, postId);
                    pstmt.setString(2, THUMBNAIL_ID_VALUE);
                    pstmt.setInt(3, imageId);
                    
                    pstmt.executeUpdate();
                }
            }
        }
        
        return rowAffected;
    }

    /**
     * Cria a conexao com o banco de dados
     */
    private void init() throws ClassNotFoundException, SQLException {
        Class.forName(JDBC_DRIVER_NAME);
        String url = "jdbc:mysql://" + mysqlDatabaseHost + "/" + mysqlDatabase; // a JDBC url
        connection = DriverManager.getConnection(url, mysqlDatabaseUser, mysqlDatabasePassword);
    }

    /**
     * Retorna os posts dentro de um intervalo
     * 
     * @param beginDate a data de inicio do intervalo
     * @param endDate a data de fim do intervalo
     * 
     * @return os posts nesse intervalo
     */
    public List<WPPost> get(Date beginDate, Date endDate) throws SQLException {
        Statement stmt = null;
        List<WPPost> posts = new ArrayList<WPPost>();
        StringBuilder sql = new StringBuilder(
                "SELECT post_date,post_title,post_content,post_name,id FROM ");
                sql
                        .append("wp_").append(mysqlDatabaseCode).append("_posts")
                        .append(" WHERE post_date >= ")
                        .append("?")
                        .append(" AND post_date <= ")
                        .append("?");
                
        PreparedStatement pstmt = connection.prepareStatement(sql.toString());
   
        GregorianCalendar calendar = new GregorianCalendar();
        calendar.setTime(beginDate);
        calendar.set(GregorianCalendar.HOUR_OF_DAY, 0);
        calendar.set(GregorianCalendar.MINUTE, 0);
        calendar.set(GregorianCalendar.SECOND, 0);
        beginDate = calendar.getTime();
        
        calendar = new GregorianCalendar();
        calendar.setTime(endDate);
        calendar.set(GregorianCalendar.HOUR_OF_DAY, 23);
        calendar.set(GregorianCalendar.MINUTE, 59);
        calendar.set(GregorianCalendar.SECOND, 59);
        endDate = calendar.getTime();
        
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        
        pstmt.setString(1, simpleDateFormat.format(beginDate));
        pstmt.setString(2, simpleDateFormat.format(endDate));
        
        ResultSet resultSet = pstmt.executeQuery();
        
        while(resultSet.next()){
            int id = resultSet.getInt("id");
            String title = resultSet.getString(TITLE_COLUMN_NAME);
            String content = resultSet.getString(CONTENT_COLUMN_NAME);
            String link = resultSet.getString(LINK_COLUMN_NAME);
            Date date = resultSet.getDate(DATE_COLUMN_NAME);
            String image = null;
            List<String> categories = new ArrayList<String>();
            List<String> tags = new ArrayList<String>();
            
            /*** Pega a imagem ***/
            sql = new StringBuilder("SELECT meta_value FROM ");
                    sql
                            .append("wp_").append(mysqlDatabaseCode).append("_postmeta ")
                            .append("WHERE post_id = ? AND meta_key = ?");
                    
            pstmt = connection.prepareStatement(sql.toString());
            pstmt.setInt(1, id);
            pstmt.setString(2, THUMBNAIL_ID_VALUE);
            ResultSet imageIdResultSet = pstmt.executeQuery();
            
            if(imageIdResultSet.next()){
                int imageId = imageIdResultSet.getInt("meta_value");
                
                sql = new StringBuilder("SELECT guid FROM ");
                    sql
                            .append(String.format(IMAGE_TABLE_NAME, mysqlDatabaseCode))
                            .append(" WHERE id = ").append(imageId);
                    
                stmt = connection.createStatement();
                ResultSet imageLinkResultSet = stmt.executeQuery(sql.toString());
                
                if(imageLinkResultSet.next()){
                    image = imageLinkResultSet.getString("guid");
                }
            }
                    
            /*********
             * Tem que procurar onde ta o re
             *********/
            
            /*** Pega as tags/categorias ***/
            sql = new StringBuilder("SELECT term_taxonomy_id FROM ");
                    sql
                            .append("wp_").append(mysqlDatabaseCode).append("_term_relationships ")
                            .append("WHERE object_id = ").append(id);
                    
            stmt = connection.createStatement();
            ResultSet tagsAndCatsIdResultSet = stmt.executeQuery(sql.toString());
            
            while(tagsAndCatsIdResultSet.next()){
                /*** Id da tag/categoria ***/
                int tagCatId = tagsAndCatsIdResultSet.getInt("term_taxonomy_id");
                
                /*** Pega o nome da tag/categoria ***/
                sql = new StringBuilder("SELECT name FROM ");
                    sql
                            .append("wp_").append(mysqlDatabaseCode).append("_terms ")
                            .append("WHERE term_id = ").append(tagCatId);
                
                stmt = connection.createStatement();
                ResultSet tagCatNameResultSet = stmt.executeQuery(sql.toString());
                
                if(tagCatNameResultSet.next()){
                    String tagCatName = tagCatNameResultSet.getString("name");
                    
                    /*** Pega o tipo: tag ou categoria? ***/ 
                    sql = new StringBuilder("SELECT taxonomy FROM ");
                    sql
                            .append("wp_").append(mysqlDatabaseCode).append("_term_taxonomy ")
                            .append("WHERE term_id = ").append(tagCatId);
                    
                    stmt = connection.createStatement();
                    ResultSet tagCatTypeResultSet = stmt.executeQuery(sql.toString());
                    
                    if(tagCatTypeResultSet.next()){
                        String tagCatType = tagCatTypeResultSet.getString("taxonomy");
                        
                        /*** Insere na lista correta ***/
                        if(tagCatType.equalsIgnoreCase(CATEGORY_TAXONOMY_VALUE)){
                            categories.add(tagCatName);
                        }
                        else if(tagCatType.equalsIgnoreCase(TAG_TAXONOMY_VALUE)){
                            tags.add(tagCatName);
                        }
                    }
                }
            }
            
            WPPost post = new WPPost(title, content, link, image, date, categories, tags, WPPost.WPPostCharset.UTF_8);
            posts.add(post);
        }
        
        return posts;
    }
}
